﻿using System;
using System.Text;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data;
using RBS.DBUtility;
using RBS.Common;
namespace RBS.Core.DAL
{
    //U_User
    public partial class U_User
    {
        public U_User()
		{}
        public bool Exists(int Id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from U_User");
            strSql.Append(" where ");
            strSql.Append(" Id = @Id  ");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id; 
            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }
         

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(RBS.Core.Model.U_User model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into U_User(");
            strSql.Append("U_Name,Nick_Name,U_Uid,PassWord,Email,Telphone,Mobile,Sex,Birthday,QQ,Exp,Status,Reg_Time,Reg_IP");
            strSql.Append(") values (");
            strSql.Append("@U_Name,@Nick_Name,@U_Uid,@PassWord,@Email,@Telphone,@Mobile,@Sex,@Birthday,@QQ,@Exp,@Status,@Reg_Time,@Reg_IP");
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
			            new SqlParameter("@U_Name", SqlDbType.NVarChar,100) ,            
                        new SqlParameter("@Nick_Name", SqlDbType.NVarChar,100) ,            
                        new SqlParameter("@U_Uid", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@PassWord", SqlDbType.NVarChar,36) ,            
                        new SqlParameter("@Email", SqlDbType.NVarChar,100) ,            
                        new SqlParameter("@Telphone", SqlDbType.NVarChar,20) ,            
                        new SqlParameter("@Mobile", SqlDbType.NVarChar,11) ,            
                        new SqlParameter("@Sex", SqlDbType.NVarChar,12) ,            
                        new SqlParameter("@Birthday", SqlDbType.DateTime) ,            
                        new SqlParameter("@QQ", SqlDbType.NVarChar,20) ,            
                        new SqlParameter("@Exp", SqlDbType.Int,4) ,            
                        new SqlParameter("@Status", SqlDbType.TinyInt,1) ,            
                        new SqlParameter("@Reg_Time", SqlDbType.DateTime) ,            
                        new SqlParameter("@Reg_IP", SqlDbType.NVarChar,30)             
              
            };

            parameters[0].Value = model.U_Name;
            parameters[1].Value = model.Nick_Name;
            parameters[2].Value = model.U_Uid;
            parameters[3].Value = model.PassWord;
            parameters[4].Value = model.Email;
            parameters[5].Value = model.Telphone;
            parameters[6].Value = model.Mobile;
            parameters[7].Value = model.Sex;
            parameters[8].Value = model.Birthday;
            parameters[9].Value = model.QQ;
            parameters[10].Value = model.Exp;
            parameters[11].Value = model.Status;
            parameters[12].Value = model.Reg_Time;
            parameters[13].Value = model.Reg_IP;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {

                return Convert.ToInt32(obj);

            }

        }


        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(RBS.Core.Model.U_User model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update U_User set ");

            strSql.Append(" U_Name = @U_Name , ");
            strSql.Append(" Nick_Name = @Nick_Name , ");
            strSql.Append(" U_Uid = @U_Uid , ");
            strSql.Append(" PassWord = @PassWord , ");
            strSql.Append(" Email = @Email , ");
            strSql.Append(" Telphone = @Telphone , ");
            strSql.Append(" Mobile = @Mobile , ");
            strSql.Append(" Sex = @Sex , ");
            strSql.Append(" Birthday = @Birthday , ");
            strSql.Append(" QQ = @QQ , ");
            strSql.Append(" Exp = @Exp , ");
            strSql.Append(" Status = @Status , ");
            strSql.Append(" Reg_Time = @Reg_Time , ");
            strSql.Append(" Reg_IP = @Reg_IP  ");
            strSql.Append(" where Id=@Id ");

            SqlParameter[] parameters = {
			            new SqlParameter("@Id", SqlDbType.Int,4) ,            
                        new SqlParameter("@U_Name", SqlDbType.NVarChar,100) ,            
                        new SqlParameter("@Nick_Name", SqlDbType.NVarChar,100) ,            
                        new SqlParameter("@U_Uid", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@PassWord", SqlDbType.NVarChar,36) ,            
                        new SqlParameter("@Email", SqlDbType.NVarChar,100) ,            
                        new SqlParameter("@Telphone", SqlDbType.NVarChar,20) ,            
                        new SqlParameter("@Mobile", SqlDbType.NVarChar,11) ,            
                        new SqlParameter("@Sex", SqlDbType.NVarChar,12) ,            
                        new SqlParameter("@Birthday", SqlDbType.DateTime) ,            
                        new SqlParameter("@QQ", SqlDbType.NVarChar,20) ,            
                        new SqlParameter("@Exp", SqlDbType.Int,4) ,            
                        new SqlParameter("@Status", SqlDbType.TinyInt,1) ,            
                        new SqlParameter("@Reg_Time", SqlDbType.DateTime) ,            
                        new SqlParameter("@Reg_IP", SqlDbType.NVarChar,30)             
              
            };

            parameters[0].Value = model.Id;
            parameters[1].Value = model.U_Name;
            parameters[2].Value = model.Nick_Name;
            parameters[3].Value = model.U_Uid;
            parameters[4].Value = model.PassWord;
            parameters[5].Value = model.Email;
            parameters[6].Value = model.Telphone;
            parameters[7].Value = model.Mobile;
            parameters[8].Value = model.Sex;
            parameters[9].Value = model.Birthday;
            parameters[10].Value = model.QQ;
            parameters[11].Value = model.Exp;
            parameters[12].Value = model.Status;
            parameters[13].Value = model.Reg_Time;
            parameters[14].Value = model.Reg_IP;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int Id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from U_User ");
            strSql.Append(" where Id=@Id");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;


            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 批量删除一批数据
        /// </summary>
        public bool DeleteList(string U_Idlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from U_User ");
            strSql.Append(" where ID in (" + U_Idlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public RBS.Core.Model.U_User GetModel(int Id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Id, U_Name, Nick_Name, U_Uid, PassWord, Email, Telphone, Mobile, Sex, Birthday, QQ, Exp, Status, Reg_Time, Reg_IP  ");
            strSql.Append("  from U_User ");
            strSql.Append(" where Id=@Id");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;


            RBS.Core.Model.U_User model = new RBS.Core.Model.U_User();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);

            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["Id"].ToString() != "")
                {
                    model.Id = int.Parse(ds.Tables[0].Rows[0]["Id"].ToString());
                }
                model.U_Name = ds.Tables[0].Rows[0]["U_Name"].ToString();
                model.Nick_Name = ds.Tables[0].Rows[0]["Nick_Name"].ToString();
                model.U_Uid = ds.Tables[0].Rows[0]["U_Uid"].ToString();
                model.PassWord = ds.Tables[0].Rows[0]["PassWord"].ToString();
                model.Email = ds.Tables[0].Rows[0]["Email"].ToString();
                model.Telphone = ds.Tables[0].Rows[0]["Telphone"].ToString();
                model.Mobile = ds.Tables[0].Rows[0]["Mobile"].ToString();
                model.Sex = ds.Tables[0].Rows[0]["Sex"].ToString();
                if (ds.Tables[0].Rows[0]["Birthday"].ToString() != "")
                {
                    model.Birthday = DateTime.Parse(ds.Tables[0].Rows[0]["Birthday"].ToString());
                }
                model.QQ = ds.Tables[0].Rows[0]["QQ"].ToString();
                if (ds.Tables[0].Rows[0]["Exp"].ToString() != "")
                {
                    model.Exp = int.Parse(ds.Tables[0].Rows[0]["Exp"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Status"].ToString() != "")
                {
                    model.Status = int.Parse(ds.Tables[0].Rows[0]["Status"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Reg_Time"].ToString() != "")
                {
                    model.Reg_Time = DateTime.Parse(ds.Tables[0].Rows[0]["Reg_Time"].ToString());
                }
                model.Reg_IP = ds.Tables[0].Rows[0]["Reg_IP"].ToString();

                return model;
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM U_User ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" * ");
            strSql.Append(" FROM U_User ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }


        #region  ExtensionMethod
        /// <summary>
        /// 获得查询分页数据
        /// </summary>
        public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * FROM U_User ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where 1=1 " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder));
        }
        #endregion  ExtensionMethod



        public bool Exists(string U_Uid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from U_User");
            strSql.Append(" where ");
            strSql.Append(" U_Uid = @U_Uid  ");
            SqlParameter[] parameters = {
					new SqlParameter("@U_Uid", SqlDbType.VarChar,50)
			};
            parameters[0].Value = U_Uid;
            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 是否注册，
        /// </summary>
        /// <param name="mail">邮件，手机</param>
        /// <returns></returns>
        public bool IsReg(string mail)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from U_User");
            strSql.Append(" where ");
            strSql.Append(" Email = @U_Uid or  Mobile =@U_Uid ");
            SqlParameter[] parameters = {
					new SqlParameter("@U_Uid", SqlDbType.VarChar,50)
			};
            parameters[0].Value = mail;
            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        public Model.U_User Login(string name, string pwd)
        { 
            var strSql = new StringBuilder();
            strSql.Append("select Id, U_Name, Nick_Name, U_Uid, PassWord, Email, Telphone, Mobile, Sex, Birthday, QQ, Exp, Status, Reg_Time, Reg_IP  ");
            strSql.Append("  from U_User ");
            strSql.Append(" where (U_Uid=@U_Uid or Email=@U_Uid or Mobile=@U_Uid) and PassWord=@PassWord ");
            SqlParameter[] parameters = {
					new SqlParameter("@U_Uid", SqlDbType.VarChar,50),
                    new SqlParameter("@PassWord", SqlDbType.VarChar,100)
			};
            parameters[0].Value = name;
            parameters[1].Value = pwd;  
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count>0)
              return DataRowToModel(ds.Tables[0].Rows[0]);
            else
            {
                return null;
            }
        }

        public Model.U_User DataRowToModel(DataRow row)
        { 
            if (row == null)
            {
                return null;
            }
            var model = new Model.U_User(); 
            if (row["Id"].ToString() != "")
            {
                model.Id = int.Parse(row["Id"].ToString());
            }
            model.U_Name = row["U_Name"].ToString();
            model.Nick_Name = row["Nick_Name"].ToString();
            model.U_Uid = row["U_Uid"].ToString();
            model.PassWord = row["PassWord"].ToString();
            model.Email = row["Email"].ToString();
            model.Telphone = row["Telphone"].ToString();
            model.Mobile = row["Mobile"].ToString();
            model.Sex = row["Sex"].ToString();
            if (row["Birthday"].ToString() != "")
            {
                model.Birthday = DateTime.Parse(row["Birthday"].ToString());
            }
            model.QQ = row["QQ"].ToString();
            if (row["Exp"].ToString() != "")
            {
                model.Exp = int.Parse(row["Exp"].ToString());
            }
            if (row["Status"].ToString() != "")
            {
                model.Status = int.Parse(row["Status"].ToString());
            }
            if (row["Reg_Time"].ToString() != "")
            {
                model.Reg_Time = DateTime.Parse(row["Reg_Time"].ToString());
            }
            model.Reg_IP = row["Reg_IP"].ToString(); 
            return model;
        }


        /// <summary>
        /// 获取用户代办
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public DataTable GetUserMain(int userId)
        {
            const string strSql = "select * from v_usermain where id=@id";
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)
			};
            parameters[0].Value = userId;
            return DbHelperSQL.Query(strSql, parameters).Tables[0];
        }
    }
}

